package SpdgBI.PhErp;

import SpdgBI.Common.DBUtils;
import SpdgBI.Common.Udfs;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import static com.mchange.v2.c3p0.dbms.OracleUtils.createTemporaryCLOB;

public class CarryForward {

    public void PumpHisData(String PhName){
        String strSqlCfList = "SELECT cf.CarryForwardId,\n" +
                "       Datename(YEAR,cf.StartDate) AS DIMYEAR,\n" +
                "       Datename(MONTH,cf.StartDate) AS DIMMONTH,\n" +
                "  cf.CarryForwardState,\n" +
                "  cf.IsSettleAccounts,\n" +
                "  f.DisplayCode,\n" +
                "  CONVERT(CHAR(10), cf.StartDate, 121) AS ReportStartDate,\n" +
                "  CONVERT(CHAR(10), cf.EndDate, 121) AS ReportEndDate,\n" +
                "  cf.SellBackTotal,\n" +
                "  (SELECT COUNT(cfb.BookId)\n" +
                "   FROM CarryForwardBook cfb\n" +
                "   WHERE cf.CarryForwardId = cfb.CarryForwardId) CFKindAmount,\n" +
                "  cfpSum.CFBookAmount AS CFBookAmount,\n" +
                "  cfpSum.CarryForwardCost AS CarryForwardCost,\n" +
                "  cfpSum.CFListIncome AS CFListIncome,\n" +
                "  cfpSum.CFDiscrepancy AS CFDiscrepancy,\n" +
                "  ISNULL(cfpdSum.CFSelectAmount, 0) AS CFSelectAmount,\n" +
                "  ISNULL(cfpdSum.CFSelectCost, 0) AS CFSelectCost,\n" +
                "  ISNULL(cfpdSum.ListSelectIncome, 0) AS ListSelectIncome,\n" +
                "  f.PZCode,\n" +
                "  V.LCHDisplaycode,  \n" +
                "  f.FormSubmitter Submitter_NAME,\n" +
                "  f.Auditor Auditor_NAME,\n" +
                "  f.Approver Approver_NAME,\n" +
                "  f.AccountKeeper AccountKeeper_NAME,\n" +
                "  CONVERT(CHAR(10), f.FormSubmitTime, 121) FormSubmitTime1,\n" +
                "  CONVERT(CHAR(10), f.AKTime, 121) AKTime,\n" +
                "  convert(CHAR(10),cf.StatisticTime,121) StatisticTime  \n" +
                "FROM CarryForward cf\n" +
                "  INNER JOIN (SELECT cf.CarryForwardId,\n" +
                "                SUM(cfp.CFAmount) AS CFBookAmount,\n" +
                "                SUM(cfp.CFCost) AS CarryForwardCost,\n" +
                "                SUM(cfp.CFAmount * bk.BookPrice) AS CFListIncome,\n" +
                "                SUM(cfp.CFAmount * bk.BookPrice - cfp.CFCost) AS CFDiscrepancy\n" +
                "              FROM CarryForward cf\n" +
                "                LEFT JOIN CarryForwardProject cfp\n" +
                "                  ON cf.CarryForwardId = cfp.CarryForwardId\n" +
                "                LEFT JOIN Book bk\n" +
                "                  ON cfp.BookId = bk.BookId\n" +
                "              WHERE cf.CarryForwardId IS NOT NULL\n" +
                "              GROUP BY cf.CarryForwardId) cfpSum\n" +
                "    ON cf.CarryForwardId = cfpSum.CarryForwardId\n" +
                "  INNER JOIN (SELECT cf.CarryForwardId,\n" +
                "                SUM(cfpd.CFAmount) AS CFSelectAmount,\n" +
                "                SUM(cfpd.CFCost) AS CFSelectCost,\n" +
                "                SUM(cfpd.CFAmount * bk.BookPrice) AS ListSelectIncome\n" +
                "              FROM CarryForward cf\n" +
                "                LEFT JOIN CarryForwardProject cfpd\n" +
                "                  ON cf.CarryForwardId = cfpd.CarryForwardId\n" +
                "                LEFT JOIN Book bk\n" +
                "                  ON cfpd.BookId = bk.BookId\n" +
                "              GROUP BY cf.CarryForwardId) cfpdSum\n" +
                "    ON cfpSum.CarryForwardId = cfpdSum.CarryForwardId\n" +
                "  INNER JOIN Form f\n" +
                "    ON f.FormId = cf.CarryForwardId\n" +
                "  INNER JOIN Voucher V\n" +
                "    ON V.FormId = cf.CarryForwardId\n" +
                "    WHERE cf.CarryForwardState = 6\n";
        String strSqlCfDetail = "SELECT cfbb.bookId,cff.CarryForwardId,\n" +
                "       Datename(year,cff.StartDate) AS DIMYEAR,\n" +
                "       Datename(month,cff.StartDate) AS DIMMONTH,\n" +
                "       CONVERT(CHAR(10), cff.StartDate, 121) StartDate,\n" +
                "       CONVERT(CHAR(10), dateadd(day,1,cff.EndDate), 121) EndDate,\n" +
                "       CONVERT(CHAR(10), cff.StatisticTime, 121) StatisticTime,\n" +
                "               COUNT(cfpp.ProjectId) AS ProjectCount,\n" +
                "               SUM(cfpp.CFAmount) CFBookAmount,\n" +
                "               SUM(cfpp.CFCost) CarryForwardCost,\n" +
                "               SUM(cfpp.CFAmount * bkk.BookPrice) CFListIncome,\n" +
                "               0 as CFSELLINCOME,\n" +
                "               0 as CFSELLAMOUNT\n" +
                "               \n" +
                "             FROM CarryForwardBook cfbb\n" +
                "               INNER JOIN Book bkk\n" +
                "                 ON cfbb.BookId = bkk.BookId\n" +
                "               LEFT JOIN CarryForwardProject cfpp\n" +
                "                 ON cfpp.CarryforwardId = cfbb.CarryforwardId\n" +
                "                    AND cfpp.BookId = cfbb.BookId\n" +
                "               INNER JOIN CarryForward cff\n" +
                "                  ON cfbb.CarryForwardId = cff.CarryForwardId\n" +
                "                  where cff.CarryForwardState = 6\n" +
                "             GROUP BY cff.CarryForwardId,cfbb.BookId,cff.StartDate,cff.EndDate,cff.StatisticTime\n" +
                "         order by  cff.StartDate  ";
        String[] strDataType = {"NUMBER", "NVARCHAR2", "VARCHAR", "NCHAR", "CHAR", "CLOB"};
        List<String> lstDataType = new ArrayList<>(Arrays.asList(strDataType));
        List lstTblInfo = null;
        Connection connBI = null;
        Connection connPUBERP = null;
        PreparedStatement pstmtDp = null;
        PreparedStatement pstmtDataSrc = null;
        CallableStatement cstmtDp = null;
        ResultSet rsDataSrc = null;
        String strDel = "DELETE FROM BASE_PUB_CARRYFORWARDLIST WHERE UNITCODE = ?";
        Long endTime;
        Long StartTime;
        try {
            StartTime = System.currentTimeMillis();
            connPUBERP = DBUtils.getConnection(PhName);
            pstmtDataSrc = connPUBERP.prepareStatement(strSqlCfList);
            rsDataSrc = pstmtDataSrc.executeQuery();

            connBI = DBUtils.getConnection("SPDGBI");
            connBI.setAutoCommit(false);
            lstTblInfo = Udfs.GetTableInfo("BASE_PUB_CARRYFORWARDLIST");

            pstmtDp = connBI.prepareStatement(strDel);
            pstmtDp.setString(1,PhName);
            pstmtDp.execute();

            pstmtDp = connBI.prepareStatement(((Map)lstTblInfo.get(lstTblInfo.size() - 1)).get("sqlins").toString());

            oracle.sql.CLOB clob = null;
            while (rsDataSrc.next()) {
                pstmtDp.setString(1, PhName);
                for (int i = 1; i < lstTblInfo.size() - 1; i++) {
                    switch (lstDataType.indexOf(((Map)lstTblInfo.get(i)).get("DATA_TYPE").toString())) {
                        case 0:
                            pstmtDp.setFloat(i + 1, rsDataSrc.getFloat(((Map)lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                        case 1:
                        case 2:
                        case 3:
                        case 4:
                            pstmtDp.setString(i + 1, rsDataSrc.getString(((Map)lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                        case 5:
                            clob = createTemporaryCLOB(connBI, false, oracle.sql.CLOB.DURATION_SESSION);
                            clob.open(oracle.sql.CLOB.MODE_READWRITE);
                            clob.setString(1, rsDataSrc.getString(lstTblInfo.get(i).toString()));
                            pstmtDp.setClob(i + 1, clob);
                            break;
                        default:
                            pstmtDp.setObject(i + 1, rsDataSrc.getString(((Map)lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                    }
                }
                pstmtDp.addBatch();
            }
            pstmtDp.executeBatch();
            pstmtDp.clearParameters();
            pstmtDp.clearBatch();
            connBI.commit();


            pstmtDataSrc = connPUBERP.prepareStatement(strSqlCfDetail);
            rsDataSrc = pstmtDataSrc.executeQuery();
            lstTblInfo = Udfs.GetTableInfo("BASE_PUB_CARRYFORWARD");
            strDel = "DELETE FROM BASE_PUB_CARRYFORWARD WHERE UNITCODE = ?";
            pstmtDp = connBI.prepareStatement(strDel);
            pstmtDp.setString(1,PhName);
            pstmtDp.execute();

            pstmtDp = connBI.prepareStatement(((Map)lstTblInfo.get(lstTblInfo.size() - 1)).get("sqlins").toString());
            int j = 1;
            while (rsDataSrc.next()) {
                pstmtDp.setString(1, PhName);
                for (int i = 1; i < lstTblInfo.size() - 3; i++) {
                    switch (lstDataType.indexOf(((Map)lstTblInfo.get(i)).get("DATA_TYPE").toString())) {
                        case 0:
                            pstmtDp.setFloat(i + 1, rsDataSrc.getFloat(((Map)lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                        case 1:
                        case 2:
                        case 3:
                        case 4:
                            pstmtDp.setString(i + 1, rsDataSrc.getString(((Map)lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                        case 5:
                            clob = createTemporaryCLOB(connBI, false, oracle.sql.CLOB.DURATION_SESSION);
                            clob.open(oracle.sql.CLOB.MODE_READWRITE);
                            clob.setString(1, rsDataSrc.getString(lstTblInfo.get(i).toString()));
                            pstmtDp.setClob(i + 1, clob);
                            break;
                        default:
                            pstmtDp.setObject(i + 1, rsDataSrc.getString(((Map)lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                    }
                }
                cstmtDp = connPUBERP.prepareCall("{call Etl_Carryforward_GetSellIncome(?,?,?,?)}");
                //stmt.registerOutParameter(3, Types.INTEGER);
                cstmtDp.setInt(1, rsDataSrc.getInt("CarryForwardId"));
                cstmtDp.setInt(2, rsDataSrc.getInt("bookId"));
                cstmtDp.registerOutParameter(3, Types.DECIMAL);
                cstmtDp.registerOutParameter(4, Types.DECIMAL);
                cstmtDp.execute();
                pstmtDp.setFloat(lstTblInfo.size() - 2, cstmtDp.getFloat(3));
                pstmtDp.setFloat(lstTblInfo.size() - 1, cstmtDp.getFloat(4));
                pstmtDp.addBatch();
                if (j<1000){
                    j++;
                }
                else
                {
                    j=0;
                    pstmtDp.executeBatch();
                    pstmtDp.clearBatch();
                    connBI.commit();
                    endTime = System.currentTimeMillis();
                    System.out.println("抽取数据完成次数为:" + j +",用时：" + (endTime - StartTime));
                }

            }
            pstmtDp.executeBatch();
            pstmtDp.clearParameters();
            pstmtDp.clearBatch();
            connBI.commit();
            endTime = System.currentTimeMillis();
            System.out.println("抽取数据完成，出版单位编码为" + PhName + "的月末主营成本结转数据抽取完成，用时：" + (endTime - StartTime));

        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println("抽取数据时出错，抽取出版单位编码为" + PhName + "的数据没有成功");
        }finally {
            lstTblInfo = null;
            cstmtDp = null;
            DBUtils.close(connBI);
        }

    }

    public void PumpHisDataAll(){
        String[] PhList = {"010200", "0104", "010500", "010600", "010700", "0109","011000"};

        for (int i = 0; i < PhList.length; i++) {
            try {
                PumpHisData(PhList[i]);
            }catch (Exception ex){
                ex.printStackTrace();
                System.out.println("抽取数据时出错，抽取出版单位编码为" + PhList[i] + "的数据没有成功");
            }
        }

    }

}
